const express = require("express");
const { check, validationResult } = require('express-validator')

const { db, genid } = require("../db/DbUtils.js")
const { jwts } = require("../utils/JsonWebtoken.js")
const { checks } = require("../utils/Validator.js")
const { EPSTATE } = require("../config/state.json")
const { wordCount } = require("../utils/WordsCount.js")

const router = express.Router();


// 用户blog目录
router.post("/user", async (req, res) => {

    // token验证
    let token = req.headers.authorization
    // console.log(token);
    token = token.substring(0, token.indexOf(","))  //! token会自动加上 "," ????
    let user_id = jwts.verifyToken(token)
    if (user_id === null || user_id === undefined) {
        res.send(EPSTATE.TOKENINF)
        return
    }

    // SQL操作
    const sql_blog = "select * from `blog` where `user_id` = ?";
    let { err, rows } = await db.async.query(sql_blog, user_id)


    if (err === null && rows.length > 0) {
        res.send(rows)
    } else {
        res.send({
            error: err,
            msg: "查询失败"
        })
    }
})

// 精细查询
router.get("/search", async (req, res) => {


    //! 验证表单数据！！！！！！！！！


    let { blog_keyword, blog_category_id, page, pageSize, user_account } = req.query


    page = page == null ? 1 : page;
    pageSize = pageSize == null ? 10 : pageSize
    blog_category_id = blog_category_id == null ? null : blog_category_id
    blog_keyword = blog_keyword == null ? null : blog_keyword


    // 查询SQL语句
    let sql_search_blog = []
    let search_params = []


    //? sql记得加反引号！！！！

    if (blog_category_id) {
        sql_search_blog.push(" `blog_category_id` = ? ")
        search_params.push(blog_category_id)
    }

    if (blog_keyword) {
        sql_search_blog.push(" (`blog_title` like ? or `blog_abstract` like ? or `blog_content` like ?) ")
        search_params.push("%" + blog_keyword + "%")
        search_params.push("%" + blog_keyword + "%")
        search_params.push("%" + blog_keyword + "%")
    }

    if (user_account) {
        sql_search_blog.push(" `login`.`user_account` = ? ")
        search_params.push(user_account)
    }

    if (sql_search_blog.length > 0) {
        sql_search_blog = " where " + sql_search_blog.join(" and ")
    }

    // 进行语句拼接
    let sql_blog = " select `blog_id`, `user_id`, `blog_category_id`, `blog_create_time`, `blog_title`, `blog_abstract` from `blog` " + sql_search_blog + " order by `blog_create_time` desc limit ?, ?";

    if (user_account) {
        sql_blog = "SELECT `blog_id`, `blog_category_id`, `blog_title`, `blog_abstract`, `blog`.`user_id`, `login`.`user_id` `user_account` FROM `login` JOIN `blog` ON `login`.`user_id` = `blog`.`user_id` " + sql_search_blog + " order by `blog_create_time` desc LIMIT ?, ?;"
    }

    let params = search_params.concat([(page - 1) * pageSize, pageSize * 1])  // 数组连接方法 获取从第(page - 1) * pageSize条开始的前pageSize条数据


    // 分页总数
    let sql_page = " select count(*) from `blog` " + sql_search_blog;
    if (user_account) {
        sql_page = "SELECT `blog`.`user_id`, `login`.`user_id`, count(*) FROM `login` JOIN `blog` ON `login`.`user_id` = `blog`.`user_id` " + sql_search_blog;
    }
    let page_params = params;


    // SQL操作
    let ToSearch = await db.async.query(sql_blog, params)
    let ToPage = await db.async.query(sql_page, page_params)
    // console.log(sql_blog);
    // console.log(params);

    if (!ToSearch.err && !ToPage.err) {
        res.send({
            blog_keyword,
            blog_category_id,
            page,
            pageSize,
            rows: ToSearch.rows,
            count: ToPage.rows[0]["count(*)"]
        })
    } else {
        console.log(ToSearch.err);
        console.log(ToPage.err);
        res.send({
            msg: "查询失败"
        })
    }

})


//! 所有查询 将去除
router.get("/", async (req, res) => {

    // // token验证
    // let token = req.headers.authorization
    // let user_id = jwts.verifyToken(token)
    // if (user_id === null || user_id === undefined) {
    //     res.send(EPSTATE.TOKENINF)
    //     return
    // }
    const sql_all = "select * from `blog`"

    let { err, rows } = await db.async.query(sql_all, [])

    res.send({
        rows
    })

})


// 文章查询
router.get("/detail", async (req, res) => {

    let blog_id = req.query.blog_id


    // SQL操作
    const sql_blog = "select * from `blog` where `blog_id` = ?";
    let { err, rows } = await db.async.query(sql_blog, blog_id)


    if (err === null && rows.length > 0) {
        res.send(rows)
    } else {
        res.send({
            error: err,
            msg: "查询失败"
        })
    }

})


// 文章删除
router.delete("/delete", async (req, res) => {

    let token = req.headers.authorization
    let user_id = jwts.verifyToken(token)

    if (!user_id) {
        res.send(EPSTATE.TOKENINF)
        return
    }

    let blog_id = req.query.blog_id

    // SQL操作
    const sql_blog = "delete from `blog` where `blog_id` = ?";
    let { err, rows } = await db.async.query(sql_blog, blog_id)


    if (err === null) {
        console.log(err);
        res.send("删除成功")
    } else {
        res.send("删除失败")
    }

})


// 文章上传
router.post("/upload", checks.blogUpload(), async (req, res) => {

    // 表单验证
    let errors = validationResult(req)
    if (!errors.isEmpty()) {
        console.log(errors);
        return res.status(422).json({ errors: errors.array() })
    }

    // token验证
    let token = req.headers.authorization
    token = token.substring(0, token.indexOf(","))
    let user_id = jwts.verifyToken(token)
    if (user_id === null || user_id === undefined) {
        res.send(EPSTATE.TOKENINF)
        return
    }

    let { blog_title, blog_abstract, blog_category_id, blog_content } = req.body
    let blog_id = genid.NextId();
    let blog_create_time = new Date().getTime();

    // 统计字数
    let blog_words = await wordCount(blog_content)
    console.log("字数", blog_words);

    blog_abstract = blog_abstract || "";
    blog_category_id = blog_category_id || null;

    // SQL操作
    const sql_blog = "INSERT INTO `blog`(`blog_id`, `user_id`, `blog_create_time`, `blog_title`, `blog_content`, `blog_abstract`, `blog_category_id`, `blog_words`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    let params = [blog_id, user_id, blog_create_time, blog_title, blog_content, blog_abstract, blog_category_id, blog_words];
    let { err, rows } = await db.async.query(sql_blog, params)


    if (err === null) {
        res.send(EPSTATE.EP200)
    } else {
        console.log(err);
        res.send(EPSTATE.SQLINF)
    }
})


// 文章修改
router.put("/updata", checks.blogUpload(), async (req, res) => {

    // 表单验证
    // console.log(req.headers);
    let errors = validationResult(req)
    if (!errors.isEmpty()) {
        return res.status(422).json({ errors: errors.array() })
    }

    // token验证
    let token = req.headers.authorization
    token = token.substring(0, token.indexOf(","))
    // console.log(token);
    let user_id = jwts.verifyToken(token)
    if (user_id === null || user_id === undefined) {
        res.send(EPSTATE.TOKENINF)
        return
    }

    let { blog_title, blog_abstract, blog_category_id, blog_content, blog_id } = req.body

    blog_abstract = blog_abstract || "";
    blog_category_id = blog_category_id || null;

    // SQL操作
    const sql_blog = "UPDATE `blog` SET `user_id`=?, `blog_title`=?, `blog_content`=?, `blog_abstract`=?, `blog_category_id`=? WHERE `blog_id`=?";
    let params = [user_id, blog_title, blog_content, blog_abstract, blog_category_id, blog_id];
    let { err, rows } = await db.async.query(sql_blog, params)


    if (err === null) {
        res.send(EPSTATE.EP200)
    } else {
        console.log(err);
        res.send(EPSTATE.SQLINF)
    }
})

module.exports = router